package org.db.info.db;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import org.apache.log4j.Logger;
import org.db.info.db.meta.ColumnMeta;
import org.db.info.db.meta.TableMeta;
import com.jfinal.kit.Kv;
import com.jfinal.template.Engine;
import com.jfinal.template.Template;
import com.jfinal.template.source.ClassPathSourceFactory;

/**
 * 获取和生成表结构
 * <p><font color="red">注：如果需要跳过某些表，请修改isSkip(tableName)方法。</font></p>
 * @author Admin
 *
 */
public class DbBuilder {

	private Logger logger = Logger.getLogger(getClass());
	// 数据库类型
	private String dbType = "";
	private String dbName = "";
	private String driver = "";
	private String url = "" ;
	private String user = "";
	private String password = "";
	
	/**
	 * 
	 * @param url 数据库连接地址
	 * @param user 数据库用户
	 * @param password 数据库密码
	 */
	public DbBuilder(String url, String user, String password) {
		if (url.startsWith("jdbc:mysql")){
			this.dbType = "mysql";
			String str = url.substring(0, url.indexOf("?"));
			this.dbName = str.substring(str.lastIndexOf("/"));
			this.driver = "com.mysql.jdbc.Driver";
		} else if (url.startsWith("jdbc:oracle")){
			this.dbType = "oracle";
			this.driver = "oracle.jdbc.OracleDriver";
		} else if (url.startsWith("jdbc:sqlserver")){
			this.dbType = "sqlserver";
			this.driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		} else if (url.startsWith("jdbc:postgresql")){
			this.dbType = "postgresql";
			this.driver = "org.postgresql.Driver";
		} else if (url.startsWith("jdbc:db2")){
			this.dbType = "db2";
			this.driver = "com.ibm.db2.jcc.DB2Driver";
		} else if (url.startsWith("jdbc:hsqldb")){
			this.dbType = "hsqldb";
			this.driver = "org.hsqldb.jdbcDriver";
		} else if (url.startsWith("jdbc:derby")) {
			this.dbType = "derby";
			this.driver = "org.apache.derby.jdbc.ClientDriver";
		}
		if ("mysql".equals(dbType) && !url.contains("useInformationSchema")) {
			// 如果为MySQL，url连接后面需要要添加useInformationSchema=true参数，否则表、列注释无法获取
			url += (url.contains("?") ? "&" : "?") + "useInformationSchema=true";
		}
		this.url = url;
		this.user = user;
		this.password = password;
	}
	
	/**
	 * 判断表是否需要跳过
	 * @param tableName
	 * @return
	 */
	protected boolean isSkip(String tableName) {
		return false;
	}
	
	/**
	 * 单个数据库表结构输出到文件
	 * @param path
	 */
	public final void render(File path) {
		List<TableMeta> dbTableMetas = build();
		Engine engine = Engine.use().setSourceFactory(new ClassPathSourceFactory());
		Template template = engine.getTemplate("template/single_db-template.html");//batch_db-template.html
		String html = template.renderToString(Kv.by("tables", dbTableMetas));
		try (FileOutputStream out = new FileOutputStream(path)) {
			out.write(html.getBytes("UTF-8"));
		} catch (Exception e) {
			logger.error("生成失败！", e);
		}
	}
	
	/**
	 * 所有数据库表结构统一输出到一个文件
	 * @param batchDbTableMetas
	 * @param path
	 */
	public final void renderBatch(LinkedHashMap<String, List<TableMeta>> batchDbTableMetas, File path) {
		Engine engine = Engine.use().setSourceFactory(new ClassPathSourceFactory());
		Template template = engine.getTemplate("template/batch_db-template.html");
		String html = template.renderToString(Kv.by("batchDbTables", batchDbTableMetas));
		try (FileOutputStream out = new FileOutputStream(path)) {
			out.write(html.getBytes("UTF-8"));
		} catch (Exception e) {
			logger.error("生成失败！", e);
		}
	}
	
	/**
	 * 获取表结构并构建
	 * @return
	 */
	private List<TableMeta> build() {
		logger.info("Start to build TableMeta ...");
		List<TableMeta> dbTableMetas = new ArrayList<>();
		try {
			Connection conn = connect();
			DatabaseMetaData dbMeta = conn.getMetaData();
			// 不支持 view 生成
			ResultSet rs = dbMeta.getTables(conn.getCatalog(), "", "%", new String[]{"TABLE"});
			
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				
				if (isSkip(tableName)) {
					logger.info("Skip table：" + tableName);
					continue ;
				}
				String tableRemark = rs.getString("REMARKS");
				String primaryKey = getPrimaryKey(dbMeta, conn, tableName);
				List<ColumnMeta> tableColumns = new ArrayList<>();
				getTableInfo(conn, dbMeta, tableName, primaryKey, tableColumns);
				
				TableMeta tableMeta = new TableMeta();
				tableMeta.setDbType(dbType);
				tableMeta.setDbName(dbName);
				tableMeta.setTableName(tableName);
				tableMeta.setPrimaryKey(primaryKey);
				tableMeta.setRemark(tableRemark);
				tableMeta.setColumns(tableColumns);
				dbTableMetas.add(tableMeta);
			}
			close(rs, conn);
		} catch (Exception e) {
			logger.error("Build TableMeta Exception!", e);
		}
		logger.info("Build TableMeta Finished!");
		return dbTableMetas;
	}
	
	/**
	 * 获取所有表结构信息
	 * @return
	 */
	public final List<TableMeta> getTableMetaList() {
		return build();
	}
	
	private Connection connect() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			if (!conn.isClosed()) {
				logger.info("Connect to database success!");
			}
		} catch (Exception e) {
			logger.error("Sorry, Connect to database failed!", e);
		}
		return conn;
	}
	
	/**
	 * 获取主键
	 * @param dbMeta
	 * @param conn
	 * @param tableName
	 * @return
	 */
	private String getPrimaryKey(DatabaseMetaData dbMeta, Connection conn, String tableName) {
		String primaryKey = "";
		ResultSet rs = null;
		try {
			rs = dbMeta.getPrimaryKeys(conn.getCatalog(), null, tableName);
			int index = 0;
			while (rs.next()) {
				if (index++ > 0) {
					primaryKey += ",";
				}
				primaryKey += rs.getString("COLUMN_NAME");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
		}
		return primaryKey;
	}
	
	/**
	 * 获取表结构信息
	 * @param conn
	 * @param tableName
	 * @param columns
	 */
	private void getTableInfo(Connection conn, DatabaseMetaData dbMeta, String tableName, String primaryKeys, List<ColumnMeta> columns) {
		ResultSet rs = null;
		try {
			rs = dbMeta.getColumns(conn.getCatalog(), null, tableName, null);
			while (rs.next()) {
				ColumnMeta columnMeta = new ColumnMeta();
				String columnName = rs.getString("COLUMN_NAME");
				String columnType = rs.getString("TYPE_NAME");
				String dataType = "";
				if (columnType == null) {
					columnType = "";
				}
				int columnSize = rs.getInt("COLUMN_SIZE");
				
				if (columnSize > 0) {
					dataType = columnType + "(" + columnSize;
					int decimalDigits = rs.getInt("DECIMAL_DIGITS");// 小数位数
					if (decimalDigits > 0) {
						columnType = dataType + "," + decimalDigits;
					}
					dataType = dataType + ")";
				}
				
				if (primaryKeys != null && !"".equals(primaryKeys)) {
					String[] keys = primaryKeys.split(",");
					for (String key : keys) {
						if (columnName.equalsIgnoreCase(key)) {
							columnMeta.setPrimaryKey(true);
							break;
						}
					}
				}
				String nullable = rs.getString("IS_NULLABLE");
				String defaultValue = rs.getString("COLUMN_DEF");
				String remark = rs.getString("REMARKS");
				
				columnMeta.setTableName(tableName);
				columnMeta.setColumnName(columnName);
				columnMeta.setColumnType(columnType);
				columnMeta.setColumnSize(columnSize + "");
				columnMeta.setDataType(dataType);
				columnMeta.setNullable(nullable);
				columnMeta.setDefaultValue(defaultValue == null ? "NULL" : defaultValue);
				columnMeta.setRemark(remark == null ? "" : remark);
				columns.add(columnMeta);
			}
		} catch (Exception e) {
			logger.error("获取表结构信息异常", e);
		} finally {
			closeResultSet(rs);
		}
	}
	
	private void close(ResultSet rs, Connection conn) {
		closeResultSet(rs);
		closeConn(conn);
	}
	
	private void closeResultSet(ResultSet rs) {
		try {
			if (rs != null && !rs.isClosed()) {
				rs.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	private void closeConn(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
}